﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;

using Ivy.Common.Framework.Base;
using Ivy.Common.Framework.DataAccess;

using Hsmp.SocietyAdmin.Entity;


namespace Hsmp.SocietyAdmin.DataAccess
{
    public class SocietyDao : IDaoInterface<Society, SocietyCriteria>
    {
        #region  Variable& constant
        #endregion  Variable& constant

        #region Constructor & Destructor
        public SocietyDao() : base() { }
        #endregion Constructor & Destructor

        #region Properties
        #endregion Properties

        #region Public Methods
        #region IDaoInterface<Society,SocietyCriteria> Members

        public bool Insert(Society SocietyEntity)
        {
            bool IsOperationSuccessful = false;
            try
            {
                if (SocietyEntity.EntityState == EntityState.New)
                {
                    DbConnection DbConn = DataAccessProviderUtility.CreateConnectionWithConnectionString();
                    DbCommand CmdObj = DataAccessProviderUtility.CreateCommand();
                    CmdObj.CommandText = "Ivy_Society_Insert";
                    //CmdObj.CommandText = UserManagementSQLCommandText.InsertUser();
                    CmdObj.CommandType = CommandType.StoredProcedure;
                    //CmdObj.CommandType = CommandType.Text;

                    //DataAccessProviderUtility.AddParameter(CmdObj, "@SocietyKey", ParameterDirection.Input, DbType.Int32, SocietyEntity.SocietyKey);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@SocietyCode", ParameterDirection.Input, DbType.String, SocietyEntity.SocietyCode);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@SocietyName", ParameterDirection.Input, DbType.String, SocietyEntity.SocietyName);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@SocietyAddressLine1", ParameterDirection.Input, DbType.String, SocietyEntity.SocietyAddressLine1);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@SocietyAddressLine2", ParameterDirection.Input, DbType.String, SocietyEntity.SocietyAddressLine1);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@SocietyAddressCity", ParameterDirection.Input, DbType.String, SocietyEntity.SocietyAddressCity);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@SocietyAddressState", ParameterDirection.Input, DbType.String, SocietyEntity.SocietyAddressState);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@SocietyAddressCountry", ParameterDirection.Input, DbType.String, SocietyEntity.SocietyAddressCountry);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@SocietyAddressZip", ParameterDirection.Input, DbType.String, SocietyEntity.SocietyAddressZip);

                    DatabaseCommon.InsertStoredProcForParameterDatEntityBase(CmdObj, SocietyEntity);

                    DataAccessProviderUtility.AddParameter(CmdObj, "@SocietyKey", ParameterDirection.Output, DbType.Int32, DBNull.Value);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@Sys_DateOfCreation", ParameterDirection.Output, DbType.DateTime, DBNull.Value);

                    CmdObj.Connection = DbConn;
                    DbConn.Open();
                    CmdObj.Prepare();
                    int _NoOfRecords = CmdObj.ExecuteNonQuery();
                    if (_NoOfRecords == 1)
                    {
                        SocietyEntity.SocietyKey = Convert.ToInt32(CmdObj.Parameters["@SocietyKey"].Value);
                        SocietyEntity.Sys_DateOfCreation = Convert.ToDateTime(CmdObj.Parameters["@Sys_DateOfCreation"].Value);
                        SocietyEntity.Sys_DateOfModification = SocietyEntity.Sys_DateOfCreation;
                        SocietyEntity.EntityState = EntityState.UnChanged;
                        IsOperationSuccessful = true;
                    }
                    CmdObj.Dispose();
                    DbConn.Close();
                }
                else
                {
                    throw new Exception("For inserting new User entity data to database, [EntityState] attribute should be [New]");
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.Print(ex.Message);
                throw ex;
            }
            return IsOperationSuccessful;
        }


        public bool Update(Society SocietyEntity)
        {
            bool IsOperationSuccessful = false;
            try
            {
                if (SocietyEntity.EntityState == EntityState.Changed)
                {
                    DbConnection DbConn = DataAccessProviderUtility.CreateConnectionWithConnectionString();
                    DbCommand CmdObj = DataAccessProviderUtility.CreateCommand();
                    CmdObj.CommandText = "Ivy_Society_Update";
                    CmdObj.CommandType = CommandType.StoredProcedure;

                    DataAccessProviderUtility.AddParameter(CmdObj, "@SocietyKey", ParameterDirection.Input, DbType.Int32, SocietyEntity.SocietyKey);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@SocietyCode", ParameterDirection.Input, DbType.String, SocietyEntity.SocietyCode);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@SocietyName", ParameterDirection.Input, DbType.String, SocietyEntity.SocietyName);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@SocietyAddressLine1", ParameterDirection.Input, DbType.String, SocietyEntity.SocietyAddressLine1);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@SocietyAddressLine2", ParameterDirection.Input, DbType.String, SocietyEntity.SocietyAddressLine2);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@SocietyAddressCity", ParameterDirection.Input, DbType.String, SocietyEntity.SocietyAddressCity);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@SocietyAddressState", ParameterDirection.Input, DbType.String, SocietyEntity.SocietyAddressState);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@SocietyAddressCountry", ParameterDirection.Input, DbType.String, SocietyEntity.SocietyAddressCountry);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@SocietyAddressZip", ParameterDirection.Input, DbType.String, SocietyEntity.SocietyAddressZip);

                    DatabaseCommon.UpdateStoredProcForParameterDatEntityBase(CmdObj, SocietyEntity);

                    DataAccessProviderUtility.AddParameter(CmdObj, "@Sys_DateOfModification", ParameterDirection.Output, DbType.DateTime, DBNull.Value);

                    CmdObj.Connection = DbConn;
                    DbConn.Open();
                    CmdObj.Prepare();
                    int _NoOfRecords = CmdObj.ExecuteNonQuery();
                    if (_NoOfRecords == 1)
                    {
                        SocietyEntity.Sys_DateOfModification = Convert.ToDateTime(CmdObj.Parameters["@Sys_DateOfModification"].Value);
                        SocietyEntity.EntityState = EntityState.UnChanged;
                        IsOperationSuccessful = true;
                    }

                    CmdObj.Dispose();
                    DbConn.Close();
                }
                else
                {
                    throw new Exception("For Updating existing User entity data to database, [EntityState] attribute should be [Changed]");
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.Print(ex.Message);
                throw ex;
            }
            return IsOperationSuccessful;
        }


        public bool Delete(Society SocietyEntity)
        {
            bool IsOperationSuccessful = false;
            try
            {
                SocietyEntity.EntityState = EntityState.Changed;
                SocietyEntity.Sys_IsDeleted = true;
                if (Update(SocietyEntity))
                {
                    IsOperationSuccessful = true;
                }


            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.Print(ex.Message);
                throw ex;
            }
            return IsOperationSuccessful;
        }

        public Society GetByID(string ID)
        {
            Society lSelectedSociety = null; //IMPORTANT NOTE:- Just declare the variable never create an instance, create instance only when required.
            try
            {
                DbConnection DbConn = DataAccessProviderUtility.CreateConnectionWithConnectionString();
                DbCommand CmdObj = DataAccessProviderUtility.CreateCommand();
                CmdObj.CommandText = "Ivy_Society_GetByID";
                CmdObj.CommandType = CommandType.StoredProcedure;

                DataAccessProviderUtility.AddParameter(CmdObj, "@SocietyKey", ParameterDirection.Input, DbType.Int32, Convert.ToInt32(ID));

                CmdObj.Connection = DbConn;
                DbConn.Open();
                CmdObj.Prepare();
                DbDataReader _DataReader = CmdObj.ExecuteReader();

                if (_DataReader.HasRows)
                {
                    if (_DataReader.Read())
                    {
                        lSelectedSociety = new Society(); //IMPORTANT NOTE:- Create instance only when required.
                        TranslateSocietyFieldsFromReaderToEntity(lSelectedSociety, _DataReader);
                    }
                }
                else
                {
                    throw new Exception("No User found");
                }
                CmdObj.Dispose();
                DbConn.Close();
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.Print(ex.Message);
                throw ex;
            }
            return lSelectedSociety;
        }


        public List<Society> GetAll()
        
        {
            List<Society> lSocietyList = new List<Society>();
            try
            {
                DbConnection DbConn = DataAccessProviderUtility.CreateConnectionWithConnectionString();
                DbCommand CmdObj = DataAccessProviderUtility.CreateCommand();
                CmdObj.CommandText = "Ivy_Society_GetAll";
                CmdObj.CommandType = CommandType.StoredProcedure;

                CmdObj.Connection = DbConn;
                DbConn.Open();
                CmdObj.Prepare();

                DbDataReader _DataReader = CmdObj.ExecuteReader();
                if (_DataReader.HasRows)
                {
                    while (_DataReader.Read())
                    {
                        var lSocietytemp = new Society(); //IMPORTANT NOTE:- Create instance only when required.
                        this.TranslateSocietyFieldsFromReaderToEntity(lSocietytemp, _DataReader);
                        lSocietyList.Add(lSocietytemp);
                    }
                }
                CmdObj.Dispose();
                DbConn.Close();
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.Print(ex.Message);
                throw ex;
            }

            return lSocietyList;
        }

        public List<Society> GetByCriteria(SocietyCriteria SocietyCriteria)
        {
            var lSocietyList = new List<Society>();
            // SqlDataReader _DataReader = null;  - this is needed for the commented out code

            try
            {
                DbConnection DbConn = DataAccessProviderUtility.CreateConnectionWithConnectionString();
                DbCommand CmdObj = DataAccessProviderUtility.CreateCommand();
                CmdObj.CommandText = "Ivy_Society_GetByCriteria";
                CmdObj.CommandType = CommandType.StoredProcedure;

                DataAccessProviderUtility.AddParameter(CmdObj, "@SocietyCode", ParameterDirection.Input, DbType.String, SocietyCriteria.SocietyCode);
                DataAccessProviderUtility.AddParameter(CmdObj, "@SocietyName", ParameterDirection.Input, DbType.String, SocietyCriteria.SocietyName);
                DataAccessProviderUtility.AddParameter(CmdObj, "@SocietyAddressLine1", ParameterDirection.Input, DbType.String, SocietyCriteria.SocietyAddressLine1);
                DataAccessProviderUtility.AddParameter(CmdObj, "@SocietyAddressLine2", ParameterDirection.Input, DbType.String, SocietyCriteria.SocietyAddressLine2);
                DataAccessProviderUtility.AddParameter(CmdObj, "@SocietyAddressCity", ParameterDirection.Input, DbType.String, SocietyCriteria.SocietyAddressCity);
                DataAccessProviderUtility.AddParameter(CmdObj, "@SocietyAddressState", ParameterDirection.Input, DbType.String, SocietyCriteria.SocietyAddressState);
                DataAccessProviderUtility.AddParameter(CmdObj, "@SocietyAddressCountry", ParameterDirection.Input, DbType.String, SocietyCriteria.SocietyAddressCountry);
                DataAccessProviderUtility.AddParameter(CmdObj, "@SocietyAddressZip", ParameterDirection.Input, DbType.String, SocietyCriteria.SocietyAddressZip);
                
                //DatabaseCommon.DefaultStoredProcParameterValuesForGetByCriteria(CmdObj, userCriteria); Not sure if this method will allow the SP to return the data

                CmdObj.Connection = DbConn;
                DbConn.Open();
                CmdObj.Prepare();

                DbDataReader _DataReader = CmdObj.ExecuteReader();

                /*
                 * This code works *
                DbConnection DbConn = DataAccessProviderUtility.CreateConnectionWithConnectionString();
                DbConn.Open();

                SqlCommand CmdObj = new SqlCommand("Ivy_User_GetByCriteria", (SqlConnection)DbConn);

                CmdObj.CommandType = CommandType.StoredProcedure;

                CmdObj.Parameters.Add(new SqlParameter("@UserLoginID", userCriteria.UserLoginID));

                _DataReader = CmdObj.ExecuteReader();
                 * This code works *
                */

                if (_DataReader.HasRows)
                {
                    while (_DataReader.Read())
                    {
                        Society lSocietyTemp = new Society();
                        this.TranslateSocietyFieldsFromReaderToEntity(lSocietyTemp, _DataReader);
                        lSocietyList.Add(lSocietyTemp);
                    }

                }
                CmdObj.Dispose();
                DbConn.Dispose();
            }
            catch (Exception ex)
            {

                System.Diagnostics.Debug.Print(ex.Message);
                throw ex;
            }

            return lSocietyList;
        }

        #endregion
        #endregion Public Methods

        #region Events
        #endregion Events

        #region Private Methods
        private void TranslateSocietyFieldsFromReaderToEntity(Society pSociety, DbDataReader pDbDataReader)
        {
            try
            {
                pSociety.SocietyKey = Convert.ToInt32(pDbDataReader["SocietyKey"]);
                pSociety.SocietyCode = Convert.ToString(pDbDataReader["SocietyCode"]);
                pSociety.SocietyName = Convert.ToString(pDbDataReader["SocietyName"]);
                pSociety.SocietyAddressLine1 = Convert.ToString(pDbDataReader["SocietyAddressLine1"]);
                pSociety.SocietyAddressLine2 = Convert.ToString(pDbDataReader["SocietyAddressLine2"]);
                pSociety.SocietyAddressCity = Convert.ToString(pDbDataReader["SocietyAddressCity"]);
                pSociety.SocietyAddressState = Convert.ToString(pDbDataReader["SocietyAddressState"]);
                pSociety.SocietyAddressCountry = Convert.ToString(pDbDataReader["SocietyAddressCountry"]);
                pSociety.SocietyAddressZip = Convert.ToString(pDbDataReader["SocietyAddressZip"]);


                DatabaseCommon.TranslateSystemFieldsFromReaderToEntity(pSociety, pDbDataReader);
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.Print(ex.Message);
                throw ex;
            }
        }
        #endregion Private Methods



    }
}
